package com.kkb.dao.impl;

import com.kkb.bean.User;
import com.kkb.dao.BaseAdminDao;
import com.kkb.dao.BaseUserDao;
import com.kkb.utils.DateFormatUtil;
import com.kkb.utils.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class UserDaoMysql implements BaseUserDao {

    private static final String SQL_UPDATE_LOGIN_TIME = "UPDATE USER SET LOGINTIME = ? ,LOGINIP = ? WHERE PHONENUMBER =?";
    private static final String SQL_LOGIN = "SELECT * FROM USER WHERE USERNAME=? AND PASSWORD =?";
    private static final String SQL_UPDATE = "UPDATE USER SET USERNAME=?,PASSWORD=?,IDCARD=?,PHONENUMBER=? WHERE ID=?";
    private static final String SQL_FIND_BY_PHONE = "SELECT * FROM USER WHERE PHONENUMBER=?";
    private static final String SQL_INSERT = "INSERT INTO USER(USERNAME,PASSWORD,IDCARD,PHONENUMBER,CREATETIME) VALUES(?,?,?,?,NOW())";
    private static final String SQL_DELETE = "DELETE FROM USER WHERE ID = ? ";
    private static final String SQL_FIND_ALL = "SELECT * FROM USER";
    private static final String SQL_FIND_BY_LIMIT = "SELECT * FROM USER LIMIT ?,?";
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) DATA1 , COUNT(TO_DAYS(CREATETIME) = TO_DAYS(NOW()) OR NULL)  DATA2 FROM USER ";
    private static final String SQL_IS_LOGIN = "SELECT ID FROM USER WHERE PHONENUMBER = ?";
    private static final String SQL_REGISTER = "INSERT INTO USER(PHONENUMBER,CREATETIME) VALUES(?,NOW())";
    /**
     * 判断是否是已注册的用户
     *
     * @param userPhone 用户手机号
     * @return true 已登录
     */
    @Override
    public boolean isLogin(String userPhone) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_IS_LOGIN);
            // 3. 填充参数
            preparedStatement.setString(1, userPhone);
            // 4. 执行
            rs = preparedStatement.executeQuery();
            if (rs.next()){
                return true;
            } else{
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,rs);
        }
        return false;
    }

    /**
     * 根据用户手机号注册
     *
     * @param userPhone 手机号
     * @return true，注册成功，false，注册失败
     */
    @Override
    public boolean register(String userPhone) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_REGISTER);
            // 3. 填充参数
            preparedStatement.setString(1, userPhone);
            // 4. 执行
            return  preparedStatement.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 根据用户名 更新登录时间和规范
     *
     * @param userPhone
     * @param date
     * @param ip
     */
    @Override
    public void updateLoginTime(String userPhone, Date date, String ip) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_UPDATE_LOGIN_TIME);
            // 3. 填充参数
            preparedStatement.setString(1, DateFormatUtil.format(date));
            preparedStatement.setString(2,ip);
            preparedStatement.setString(3,userPhone);
            // 4. 执行
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
    }

    /**
     * 管理员根据账号密码登录
     *
     * @param username 账号
     * @param password 密码
     * @return 用户信息
     */
    @Override
    public User login(String username, String password) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_LOGIN);
            // 3. 填充参数
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            // 4. 执行
            rs = preparedStatement.executeQuery();
            // 5. 查询结果
            User user = null;
            if (rs.next()){
                int id = rs.getInt("id");
                String username1 = rs.getString("username");
                String password1 = rs.getString("password");
                String idCard = rs.getString("idcard");
                String phoneNumber = rs.getString("phonenumber");
                String loginIp = rs.getString("loginip");
                Date loginTime = rs.getDate("logintime");
                Date createTime = rs.getDate("createtime");
                user = new User(id,username1,password1,idCard,phoneNumber,loginIp,loginTime,createTime);
            }
            return  user;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return null;
    }

    /**
     * 根据用户手机号码查找 用户信息
     *
     * @param phoneNumber 手机号
     * @return 查找到的用户信息
     */
    @Override
    public User findByPhoneNumber(String phoneNumber) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_PHONE);
            // 3. 填充参数
            preparedStatement.setString(1,phoneNumber);
            // 4. 执行
            rs = preparedStatement.executeQuery();
            // 5. 查询结果
            User user = null;
            if (rs.next()){
                int id = rs.getInt("id");
                String username1 = rs.getString("username");
                String password1 = rs.getString("password");
                String idCard = rs.getString("idcard");
                String loginIp = rs.getString("loginip");
                Date loginTime = rs.getDate("logintime");
                Date createTime = rs.getDate("createtime");
                user = new User(id,username1,password1,idCard,phoneNumber,loginIp,loginTime,createTime);
            }
            return  user;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,rs);
        }
        return null;
    }

    /**
     * 添加用户
     *
     * @param user 用户信息
     * @return true 添加成功，false失败
     */
    @Override
    public boolean insert(User user) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            // 3. 填充参数
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getIdCard());
            preparedStatement.setString(4,user.getPhoneNumber());
            // 4. 执行
            return preparedStatement.executeUpdate() > 0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 根据用户id 修改用户信息
     *
     * @param id   用户id
     * @param user 修改的用户信息
     * @return 修改的结果 true表示成功，false失败
     */
    @Override
    public boolean update(int id, User user) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            // 3. 填充参数
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getIdCard());
            preparedStatement.setString(4,user.getPhoneNumber());
            preparedStatement.setInt(5, id);
            // 4. 执行
            return preparedStatement.executeUpdate() > 0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 根据用户id ， 删除用户
     *
     * @param id 用户id
     * @return 删除的结果
     */
    @Override
    public boolean delete(int id) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            // 3. 填充参数
            preparedStatement.setInt(1, id);
            // 4. 执行
            return preparedStatement.executeUpdate() > 0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 查询用户信息列表
     *
     * @param limit      是否分页
     * @param offset     从第几条数据开始
     * @param pageNumber 查询多少条
     * @return
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            // 2. 预编译sql语句
            if (limit){
                preparedStatement = connection.prepareStatement(SQL_FIND_BY_LIMIT);
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNumber);
            } else{
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
            }


            // 4. 执行
            rs = preparedStatement.executeQuery();
            // 5. 查询结果
            List<User> list = new ArrayList<>();
            while (rs.next()){
                int id = rs.getInt("id");
                String username1 = rs.getString("username");
                String password1 = rs.getString("password");
                String userPhone = rs.getString("phonenumber");
                String idCard = rs.getString("idcard");
                String loginIp = rs.getString("loginip");
                Date loginTime = rs.getDate("logintime");
                Date createTime = rs.getDate("createtime");
                User user = new User(id,username1,password1,idCard,userPhone,loginIp,loginTime,createTime);
                list.add(user);
            }
            return  list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,rs);
        }
        return null;
    }

    /**
     * 查询日新增用户数
     * 总的用户数
     *
     * @return map
     */
    @Override
    public Map<String, Integer> console() {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            // 2. 预编译sql语句
                preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            // 4. 执行
            rs = preparedStatement.executeQuery();
            // 5. 查询结果
            HashMap<String, Integer> map = new HashMap<>();
            if (rs.next()){
                map.put("data1",rs.getInt("DATA1"));
                map.put("data2",rs.getInt("DATA2"));
            }
            return  map;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,rs);
        }
        return null;
    }
}
